"""
模板填充器

读取日文模板文件，填入计算后的资源分配数据
"""

import logging
import pandas as pd
from pathlib import Path
from openpyxl import load_workbook
from datetime import date, datetime
from typing import List, Dict, Any
import calendar
import yaml
import shutil

from src.models.simple_models import MonthlyAllocation, RevisionHistoryEntry


class TemplateFiller:
    """模板填充器类"""

    def __init__(self, template_path: str, config_path: str = "config.yaml"):
        """
        初始化模板填充器

        Args:
            template_path: 模板文件路径
            config_path: 配置文件路径
        """
        self.template_path = template_path
        self.config_path = config_path
        self.logger = logging.getLogger(__name__)
        self._load_config()

    def fill_template_with_data(self, monthly_allocations: List[MonthlyAllocation],
                               project_info: Any, output_path: str) -> bool:
        """
        复制模板文件并填充数据 - 保持原模板格式不变

        Args:
            monthly_allocations: 月度分配数据
            project_info: 项目信息
            output_path: 输出文件路径

        Returns:
            bool: 是否成功
        """
        try:
            self.logger.info(f"开始从Template复制模板：{self.template_path}")

            # 确保输出目录存在
            output_file = Path(output_path)
            output_file.parent.mkdir(parents=True, exist_ok=True)

            # 步骤1: 复制模板文件到输出位置
            shutil.copy2(self.template_path, output_file)
            self.logger.info(f"模板文件已复制到：{output_file}")

            # 步骤2: 加载复制后的文件进行填充 (保持原格式，移除可能有问题的参数)
            wb = load_workbook(output_file, data_only=False)
            self.logger.info(f"加载复制后的文件，工作表: {wb.sheetnames}")

            # 步骤3: 准备数据
            summary_data = self._prepare_summary_data(monthly_allocations)

            # 步骤4: 填充改訂履歴
            if '改訂履歴' in wb.sheetnames:
                self.logger.info("开始填充改訂履歴...")
                self._fill_revision_history_data_only(wb['改訂履歴'])
                self.logger.info("改訂履歴填充完成")
            else:
                self.logger.warning(f"未找到改訂履歴工作表，可用工作表: {wb.sheetnames}")

            # 步骤5: 填充作業工程別要員計画工作表
            if '作業工程別要員計画' in wb.sheetnames:
                self.logger.info("开始填充作業工程別要員計画...")
                self._fill_work_plan_data_only(wb['作業工程別要員計画'], monthly_allocations)
                self.logger.info("作業工程別要員計画填充完成")
            else:
                self.logger.warning(f"未找到作業工程別要員計画工作表，可用工作表: {wb.sheetnames}")

            # 步骤6: 动态更新リソース計画工作表
            if 'リソース計画' in wb.sheetnames:
                self.logger.info("开始动态更新リソース計画...")
                self._fill_resource_plan_data_only_dynamic(wb['リソース計画'], monthly_allocations)
                self.logger.info("リソース計画动态更新完成")
            else:
                self.logger.warning(f"未找到リソース計画工作表，可用工作表: {wb.sheetnames}")

            # 步骤7: 保存文件 (保持原始格式)
            try:
                # 直接保存，保持原始Excel文件的所有属性
                wb.save(output_file)
                self.logger.info("文件保存成功")

                # 验证文件是否可读
                try:
                    test_wb = load_workbook(output_file, read_only=True)
                    test_wb.close()
                    self.logger.info("文件格式验证成功")
                except Exception as verify_error:
                    self.logger.warning(f"文件验证警告: {verify_error}")

            except Exception as save_error:
                self.logger.error(f"保存文件时出错: {save_error}")
                raise save_error
            self.logger.info(f"模板填充完成：{output_file}")
            return True

        except Exception as e:
            self.logger.error(f"模板填充失败：{e}")
            import traceback
            traceback.print_exc()
            return False

    def _prepare_data_matrix(self, monthly_allocations: List[MonthlyAllocation]) -> Dict:
        """
        准备数据矩阵

        Args:
            monthly_allocations: 月度分配数据

        Returns:
            Dict: 数据矩阵 {role: {month: count}}
        """
        # 获取所有月份和角色
        all_months = sorted(set(alloc.month for alloc in monthly_allocations))
        all_roles = sorted(set(alloc.role for alloc in monthly_allocations))

        # 构建数据矩阵
        data_matrix = {
            'months': all_months,
            'roles': all_roles,
            'data': {}
        }

        # 初始化数据矩阵
        for role in all_roles:
            data_matrix['data'][role] = {}
            for month in all_months:
                data_matrix['data'][role][month] = 0

        # 填入实际数据
        for alloc in monthly_allocations:
            data_matrix['data'][alloc.role][alloc.month] += alloc.person_count

        return data_matrix

    def _prepare_summary_data(self, monthly_allocations: List[MonthlyAllocation]) -> Dict:
        """
        准备汇总数据

        Args:
            monthly_allocations: 月度分配数据

        Returns:
            Dict: 汇总数据
        """
        # 按月份汇总
        monthly_summary = {}
        for alloc in monthly_allocations:
            month_key = alloc.month
            if month_key not in monthly_summary:
                monthly_summary[month_key] = {'person_count': 0, 'cost': 0}
            monthly_summary[month_key]['person_count'] += alloc.person_count
            monthly_summary[month_key]['cost'] += alloc.cost

        # 按角色汇总
        role_summary = {}
        for alloc in monthly_allocations:
            if alloc.role not in role_summary:
                role_summary[alloc.role] = {'person_months': 0, 'cost': 0}
            role_summary[alloc.role]['person_months'] += alloc.person_count
            role_summary[alloc.role]['cost'] += alloc.cost

        return {
            'monthly': monthly_summary,
            'roles': role_summary,
            'total_person_months': sum(stats['person_months'] for stats in role_summary.values()),
            'total_cost': sum(stats['cost'] for stats in role_summary.values())
        }

    def _fill_resource_allocation_sheet(self, ws, data_matrix: Dict):
        """
        填充资源分配表（山積み表）

        Args:
            ws: 工作表对象
            data_matrix: 数据矩阵
        """
        self.logger.info("填充山積み表...")

        # 查找表格开始位置
        start_row, start_col = self._find_table_start(ws)

        if start_row is None:
            # 如果找不到现有表格，从A1开始创建
            start_row, start_col = 1, 1
            self._create_new_table(ws, data_matrix, start_row, start_col)
        else:
            # 填充现有表格
            self._fill_existing_table(ws, data_matrix, start_row, start_col)

    def _find_table_start(self, ws) -> tuple:
        """
        查找表格开始位置

        Args:
            ws: 工作表对象

        Returns:
            tuple: (行号, 列号) 或 (None, None)
        """
        # 扫描前20行和20列，寻找类似表头的内容
        for row in range(1, 21):
            for col in range(1, 21):
                cell_value = ws.cell(row=row, column=col).value
                if cell_value and (
                    '角色' in str(cell_value) or
                    'Role' in str(cell_value) or
                    '役割' in str(cell_value) or
                    'PM' in str(cell_value)
                ):
                    return row, col

        return None, None

    def _create_new_table(self, ws, data_matrix: Dict, start_row: int, start_col: int):
        """
        创建新的表格

        Args:
            ws: 工作表对象
            data_matrix: 数据矩阵
            start_row: 开始行
            start_col: 开始列
        """
        # 写入表头
        ws.cell(row=start_row, column=start_col, value="角色/月份")

        # 写入月份表头
        for i, month in enumerate(data_matrix['months']):
            month_str = month.strftime("%Y-%m")
            ws.cell(row=start_row, column=start_col + i + 1, value=month_str)

        # 写入角色和数据
        for i, role in enumerate(data_matrix['roles']):
            row_num = start_row + i + 1
            ws.cell(row=row_num, column=start_col, value=role)

            # 写入该角色各月份的数据
            for j, month in enumerate(data_matrix['months']):
                count = data_matrix['data'][role][month]
                col_num = start_col + j + 1
                if count > 0:
                    ws.cell(row=row_num, column=col_num, value=count)

    def _fill_existing_table(self, ws, data_matrix: Dict, start_row: int, start_col: int):
        """
        填充现有表格

        Args:
            ws: 工作表对象
            data_matrix: 数据矩阵
            start_row: 开始行
            start_col: 开始列
        """
        # 简化：清除现有数据区域并重新创建
        # 实际实现中可以更智能地匹配现有格式
        self._create_new_table(ws, data_matrix, start_row, start_col)

    def _fill_histogram_sheet(self, ws, summary_data: Dict):
        """
        填充直方图表（リソースヒストグラム）

        Args:
            ws: 工作表对象
            summary_data: 汇总数据
        """
        self.logger.info("填充リソースヒストグラム...")

        # 查找或创建表格
        start_row = 3  # 从第3行开始，留出标题空间

        # 写入表头
        ws.cell(row=start_row, column=1, value="月份")
        ws.cell(row=start_row, column=2, value="总人数")
        ws.cell(row=start_row, column=3, value="总成本")

        # 按月份排序并写入数据
        sorted_months = sorted(summary_data['monthly'].keys())
        for i, month in enumerate(sorted_months):
            row_num = start_row + i + 1
            month_data = summary_data['monthly'][month]

            ws.cell(row=row_num, column=1, value=month.strftime("%Y-%m"))
            ws.cell(row=row_num, column=2, value=month_data['person_count'])
            ws.cell(row=row_num, column=3, value=month_data['cost'])

        # 写入总计
        total_row = start_row + len(sorted_months) + 2
        ws.cell(row=total_row, column=1, value="总计")
        ws.cell(row=total_row, column=2, value=summary_data['total_person_months'])
        ws.cell(row=total_row, column=3, value=summary_data['total_cost'])

    def analyze_template_structure(self) -> Dict:
        """
        分析模板结构

        Returns:
            Dict: 模板结构信息
        """
        try:
            wb = load_workbook(self.template_path, data_only=True)
            structure = {
                'sheets': [],
                'total_sheets': len(wb.sheetnames)
            }

            for sheet_name in wb.sheetnames:
                ws = wb[sheet_name]
                sheet_info = {
                    'name': sheet_name,
                    'max_row': ws.max_row,
                    'max_column': ws.max_column,
                    'sample_data': []
                }

                # 读取前5行5列的样本数据
                for row in range(1, min(6, ws.max_row + 1)):
                    row_data = []
                    for col in range(1, min(6, ws.max_column + 1)):
                        cell_value = ws.cell(row=row, column=col).value
                        row_data.append(str(cell_value) if cell_value is not None else "")
                    sheet_info['sample_data'].append(row_data)

                structure['sheets'].append(sheet_info)

            return structure

        except Exception as e:
            self.logger.error(f"分析模板结构失败：{e}")
            return {}

    def _load_config(self):
        """加载配置文件"""
        try:
            with open(self.config_path, 'r', encoding='utf-8') as f:
                self.config = yaml.safe_load(f)
        except Exception as e:
            self.logger.warning(f"加载配置文件失败：{e}")
            # 使用默认配置
            self.config = {
                'revision_history': {
                    'default_creator': 'システム自動生成',
                    'initial_version': 'Ver.1.0',
                    'date_format': '%Y/%m/%d',
                    'content_template': '人月数由{old_person_months}変更為{new_person_months}，成本由{old_cost}変更為{new_cost}元'
                }
            }

    def _fill_revision_history(self, wb, summary_data: Dict, project_info: Any):
        """
        填充改定履历和表紙汇总信息

        Args:
            wb: 工作簿对象
            summary_data: 汇总数据
            project_info: 项目信息
        """
        self.logger.info("填充改定履历...")

        # 1. 填充改訂履歴工作表
        if '改訂履歴' in wb.sheetnames:
            self._fill_revision_history_sheet(wb['改訂履歴'])

        # 2. 填充表紙的汇总信息
        if '表紙' in wb.sheetnames or wb.sheetnames:
            cover_sheet = wb['表紙'] if '表紙' in wb.sheetnames else wb[wb.sheetnames[0]]
            self._fill_cover_summary(cover_sheet, summary_data)

        self.logger.info("改定履历填充完成")

    def _fill_revision_history_sheet(self, ws):
        """
        填充改訂履歴工作表

        Args:
            ws: 改訂履歴工作表对象
        """
        # 生成改定履历条目
        revision_entry = self._generate_revision_entry({}, None)

        # 填充改定履历数据到第3行
        revision_row = 3
        if revision_entry:
            # 版本号 (A列)
            ws.cell(row=revision_row, column=1, value=revision_entry.version)

            # 日期 (B列)
            ws.cell(row=revision_row, column=2, value=revision_entry.date)

            # 作成者/変更者 (C列)
            ws.cell(row=revision_row, column=3, value=revision_entry.creator)

            # 变更理由 (D列，暂时为空)
            ws.cell(row=revision_row, column=4, value=revision_entry.change_reason)

            # 变更内容 (E列，暂时为空)
            ws.cell(row=revision_row, column=5, value=revision_entry.content)

    def _fill_cover_summary(self, ws, summary_data: Dict):
        """
        填充表紙的月度汇总信息

        Args:
            ws: 表紙工作表对象
            summary_data: 汇总数据
        """
        if 'monthly' not in summary_data:
            return

        # 从第4行开始填充月度数据 (根据截图分析)
        start_row = 4
        current_row = start_row

        # 按月份排序并填充数据
        sorted_months = sorted(summary_data['monthly'].keys())
        for month in sorted_months:
            month_data = summary_data['monthly'][month]

            # A列: 月份 (2024-01格式)
            ws.cell(row=current_row, column=1, value=month.strftime("%Y-%m"))

            # B列: 总人数
            ws.cell(row=current_row, column=2, value=month_data['person_count'])

            # C列: 总成本
            ws.cell(row=current_row, column=3, value=month_data['cost'])

            current_row += 1

        # 填充合计行
        total_row = current_row + 1
        ws.cell(row=total_row, column=1, value="合计")
        ws.cell(row=total_row, column=2, value=summary_data['total_person_months'])
        ws.cell(row=total_row, column=3, value=summary_data['total_cost'])

    def _generate_revision_entry(self, summary_data: Dict, project_info: Any) -> RevisionHistoryEntry:
        """
        生成改定履历条目

        Args:
            summary_data: 汇总数据
            project_info: 项目信息

        Returns:
            RevisionHistoryEntry: 改定履历条目
        """
        config = self.config.get('revision_history', {})

        # 获取当前日期
        today = datetime.now()
        date_str = today.strftime(config.get('date_format', '%Y/%m/%d'))

        # 创建改定履历条目
        revision_entry = RevisionHistoryEntry(
            version=config.get('initial_version', 'Ver.1.0'),
            date=date_str,
            creator=config.get('default_creator', 'システム自動生成'),
            approver="",  # 暂时为空
            change_reason="",  # 暂时为空
            content=""  # 暂时为空，后续版本可以添加变更对比功能
        )

        return revision_entry

    def _fill_work_plan_sheet(self, ws, monthly_allocations: List[MonthlyAllocation], project_info: Any):
        """
        填充作業工程別要員計画工作表

        Args:
            ws: 工作表对象
            monthly_allocations: 月度分配数据
            project_info: 项目信息
        """
        self.logger.info("填充作業工程別要員計画...")

        # 1. 生成动态时间轴
        self._setup_dynamic_time_axis(ws, monthly_allocations)

        # 2. 准备工程数据
        work_phases = self._prepare_work_phases_data(monthly_allocations)

        # 3. 填充工程数据
        self._fill_work_phases_data(ws, work_phases, monthly_allocations)

        self.logger.info("作業工程別要員計画填充完成")

    def _setup_dynamic_time_axis(self, ws, monthly_allocations: List[MonthlyAllocation]):
        """
        设置动态时间轴

        Args:
            ws: 工作表对象
            monthly_allocations: 月度分配数据
        """
        # 获取所有月份并排序
        all_months = sorted(set(alloc.month for alloc in monthly_allocations))

        if not all_months:
            return

        # 清理现有的合并单元格
        self._clear_merged_cells(ws)

        # 设置主标题
        ws.cell(row=2, column=2, value="作業工程別要員計画")

        # 计算年份列的范围
        start_year = all_months[0].year
        end_year = all_months[-1].year
        start_col = 5  # E列开始

        # 设置第3行大分类标题 (保持合并单元格)
        try:
            ws.merge_cells('B3:D3')
            ws.cell(row=3, column=2, value="工程別作業期間")
        except Exception as e:
            self.logger.warning(f"合并单元格B3:D3失败: {e}")
            ws.cell(row=3, column=2, value="工程別作業期間")

        # 设置第3行作業計画标题 (合并月份列)
        if len(all_months) > 0:
            try:
                end_col = start_col + len(all_months) - 1
                ws.merge_cells(start_row=3, start_column=start_col,
                              end_row=3, end_column=end_col)
                ws.cell(row=3, column=start_col, value="作業計画")
            except Exception as e:
                self.logger.warning(f"合并单元格作業計画失败: {e}")
                ws.cell(row=3, column=start_col, value="作業計画")

        # 设置第4行年份标题
        current_col = start_col
        for year in range(start_year, end_year + 1):
            year_months = [m for m in all_months if m.year == year]
            if year_months:
                year_start_col = current_col
                year_end_col = current_col + len(year_months) - 1

                # 合并年份标题单元格
                if year_start_col != year_end_col:
                    try:
                        ws.merge_cells(start_row=4, start_column=year_start_col,
                                     end_row=4, end_column=year_end_col)
                    except Exception as e:
                        self.logger.warning(f"合并年份标题失败: {e}")

                ws.cell(row=4, column=year_start_col, value=f"{year}年")
                current_col += len(year_months)

        # 设置第4行固定标题
        ws.cell(row=4, column=2, value="No.")
        ws.cell(row=4, column=3, value="作業工程（フェーズ）")
        ws.cell(row=4, column=4, value="作業期間")

        # 设置第5行月份标题
        for i, month in enumerate(all_months):
            col = start_col + i
            month_str = f"{month.month}月"
            ws.cell(row=5, column=col, value=month_str)

    def _clear_merged_cells(self, ws):
        """
        清理工作表中的合并单元格

        Args:
            ws: 工作表对象
        """
        try:
            # 获取所有合并单元格范围并清理
            merged_ranges = list(ws.merged_cells.ranges)
            for merged_range in merged_ranges:
                ws.unmerge_cells(str(merged_range))
        except Exception as e:
            self.logger.warning(f"清理合并单元格失败: {e}")

    def _prepare_work_phases_data(self, monthly_allocations: List[MonthlyAllocation]):
        """
        准备工程阶段数据

        Args:
            monthly_allocations: 月度分配数据

        Returns:
            Dict: 工程阶段数据
        """
        phases_data = {}

        # 按阶段名称分组
        for alloc in monthly_allocations:
            phase_name = alloc.phase_name
            if phase_name not in phases_data:
                phases_data[phase_name] = {
                    'roles': set(),
                    'months': set(),
                    'allocations': []
                }

            phases_data[phase_name]['roles'].add(alloc.role)
            phases_data[phase_name]['months'].add(alloc.month)
            phases_data[phase_name]['allocations'].append(alloc)

        # 转换为列表并排序
        result = []
        for i, (phase_name, data) in enumerate(phases_data.items(), 1):
            sorted_months = sorted(data['months'])
            sorted_roles = sorted(data['roles'])

            # 计算期间描述
            if sorted_months:
                start_month = sorted_months[0]
                end_month = sorted_months[-1]
                duration = len(sorted_months)

                period_desc = f"{start_month.strftime('%y年%m月')}〜{end_month.strftime('%y年%m月')}（{duration}カ月）"
            else:
                period_desc = ""

            result.append({
                'no': i,
                'phase_name': phase_name,
                'period_desc': period_desc,
                'roles': sorted_roles,
                'months': sorted_months,
                'allocations': data['allocations']
            })

        return result

    def _fill_work_phases_data(self, ws, work_phases, monthly_allocations: List[MonthlyAllocation]):
        """
        填充工程阶段数据

        Args:
            ws: 工作表对象
            work_phases: 工程阶段数据
            monthly_allocations: 月度分配数据
        """
        # 获取所有月份用于列映射
        all_months = sorted(set(alloc.month for alloc in monthly_allocations))
        month_to_col = {month: 5 + i for i, month in enumerate(all_months)}

        current_row = 6  # 从第6行开始填充数据

        for phase_data in work_phases:
            phase_start_row = current_row

            # 为每个角色创建一行
            for role in phase_data['roles']:
                # 填充基本信息（只在第一行填充）
                if current_row == phase_start_row:
                    ws.cell(row=current_row, column=2, value=phase_data['no'])  # No.
                    ws.cell(row=current_row, column=3, value=phase_data['phase_name'])  # 工程名称
                    ws.cell(row=current_row, column=4, value=phase_data['period_desc'])  # 期间

                ws.cell(row=current_row, column=5, value=role)  # 角色

                # 填充月份数据
                for alloc in phase_data['allocations']:
                    if alloc.role == role and alloc.month in month_to_col:
                        col = month_to_col[alloc.month]
                        # 每月工数为1（根据要求）
                        if alloc.person_count > 0:
                            ws.cell(row=current_row, column=col, value=1)

                current_row += 1

            # 如果一个阶段有多个角色，需要合并工程信息的单元格
            if len(phase_data['roles']) > 1:
                try:
                    # 合并No.列
                    ws.merge_cells(start_row=phase_start_row, start_column=2,
                                  end_row=current_row - 1, end_column=2)

                    # 合并工程名称列
                    ws.merge_cells(start_row=phase_start_row, start_column=3,
                                  end_row=current_row - 1, end_column=3)

                    # 合并期间列
                    ws.merge_cells(start_row=phase_start_row, start_column=4,
                                  end_row=current_row - 1, end_column=4)
                except Exception as e:
                    self.logger.warning(f"合并工程信息单元格失败: {e}")

    def _fill_resource_plan_sheet(self, ws, monthly_allocations: List[MonthlyAllocation], project_info: Any):
        """
        填充リソース計画工作表

        Args:
            ws: 工作表对象
            monthly_allocations: 月度分配数据
            project_info: 项目信息
        """
        self.logger.info("填充リソース計画...")

        # 1. 设置动态时间轴
        self._setup_resource_plan_time_axis(ws, monthly_allocations)

        # 2. 准备资源数据
        resource_data = self._prepare_resource_plan_data(monthly_allocations)

        # 3. 填充角色汇总数据
        self._fill_role_summary_data(ws, resource_data, monthly_allocations)

        # 4. 填充个人分配数据
        self._fill_individual_assignment_data(ws, resource_data, monthly_allocations)

        self.logger.info("リソース計画填充完成")

    def _setup_resource_plan_time_axis(self, ws, monthly_allocations: List[MonthlyAllocation]):
        """
        设置リソース計画的动态时间轴

        Args:
            ws: 工作表对象
            monthly_allocations: 月度分配数据
        """
        # 获取所有月份并排序
        all_months = sorted(set(alloc.month for alloc in monthly_allocations))

        if not all_months:
            return

        # 清理现有的合并单元格
        self._clear_merged_cells(ws)

        # 设置主标题
        ws.cell(row=2, column=2, value="リソース計画")

        # 计算年份列的范围
        start_year = all_months[0].year
        end_year = all_months[-1].year
        start_col = 4  # D列开始

        # 设置第3行大分类标题
        try:
            ws.merge_cells('B3:C3')
            ws.cell(row=3, column=2, value="リソース計画")
        except Exception as e:
            self.logger.warning(f"合并单元格B3:C3失败: {e}")
            ws.cell(row=3, column=2, value="リソース計画")

        # 设置第3行作業計画标题 (合并月份列)
        if len(all_months) > 0:
            try:
                end_col = start_col + len(all_months) - 1
                ws.merge_cells(start_row=3, start_column=start_col,
                              end_row=3, end_column=end_col)
                ws.cell(row=3, column=start_col, value="作業計画")
            except Exception as e:
                self.logger.warning(f"合并单元格作業計画失败: {e}")
                ws.cell(row=3, column=start_col, value="作業計画")

        # 设置第4行年份标题
        current_col = start_col
        for year in range(start_year, end_year + 1):
            year_months = [m for m in all_months if m.year == year]
            if year_months:
                year_start_col = current_col
                year_end_col = current_col + len(year_months) - 1

                # 合并年份标题单元格
                if year_start_col != year_end_col:
                    try:
                        ws.merge_cells(start_row=4, start_column=year_start_col,
                                     end_row=4, end_column=year_end_col)
                    except Exception as e:
                        self.logger.warning(f"合并年份标题失败: {e}")

                ws.cell(row=4, column=year_start_col, value=f"{year}年")
                current_col += len(year_months)

        # 设置第4行固定标题
        ws.cell(row=4, column=2, value="No.")
        ws.cell(row=4, column=3, value="担当者")
        ws.cell(row=4, column=4, value="役割")

        # 设置第5行月份标题
        for i, month in enumerate(all_months):
            col = start_col + i
            month_str = f"{month.month}月"
            ws.cell(row=5, column=col, value=month_str)

    def _prepare_resource_plan_data(self, monthly_allocations: List[MonthlyAllocation]):
        """
        准备リソース計画数据

        Args:
            monthly_allocations: 月度分配数据

        Returns:
            Dict: 资源计划数据
        """
        # 按角色汇总工时
        role_summary = {}
        for alloc in monthly_allocations:
            if alloc.role not in role_summary:
                role_summary[alloc.role] = {}

            month_key = alloc.month
            if month_key not in role_summary[alloc.role]:
                role_summary[alloc.role][month_key] = 0

            # 每个人每月工时为1
            role_summary[alloc.role][month_key] += alloc.person_count

        # 生成虚拟人员分配
        individuals = []
        person_id = 0

        for role, monthly_data in role_summary.items():
            # 计算该角色需要的总人数（取最大月度人数）
            max_people = max(monthly_data.values()) if monthly_data else 0

            # 为每个角色生成虚拟人员
            for i in range(max_people):
                person_name = chr(ord('A') + person_id)  # A, B, C, D...
                person_id += 1

                individuals.append({
                    'name': person_name,
                    'role': role,
                    'monthly_hours': {}
                })

        # 将角色工时分配给个人
        for role, monthly_data in role_summary.items():
            role_individuals = [ind for ind in individuals if ind['role'] == role]

            for month, total_hours in monthly_data.items():
                # 将工时平均分配给该角色的人员
                for i, individual in enumerate(role_individuals):
                    if i < total_hours:  # 只分配给需要的人数
                        individual['monthly_hours'][month] = 1
                    else:
                        individual['monthly_hours'][month] = 0

        return {
            'role_summary': role_summary,
            'individuals': individuals
        }

    def _fill_role_summary_data(self, ws, resource_data, monthly_allocations: List[MonthlyAllocation]):
        """
        填充角色汇总数据

        Args:
            ws: 工作表对象
            resource_data: 资源数据
            monthly_allocations: 月度分配数据
        """
        # 获取所有月份用于列映射
        all_months = sorted(set(alloc.month for alloc in monthly_allocations))
        month_to_col = {month: 4 + i for i, month in enumerate(all_months)}

        # 填充角色汇总标题
        ws.cell(row=6, column=3, value="役割別必要工数")

        current_row = 7
        sorted_roles = sorted(resource_data['role_summary'].keys())

        for role in sorted_roles:
            ws.cell(row=current_row, column=4, value=role)  # 角色名称

            # 填充月度工时数据
            role_monthly_data = resource_data['role_summary'][role]
            for month, hours in role_monthly_data.items():
                if month in month_to_col:
                    col = month_to_col[month]
                    ws.cell(row=current_row, column=col, value=hours)

            current_row += 1

    def _fill_individual_assignment_data(self, ws, resource_data, monthly_allocations: List[MonthlyAllocation]):
        """
        填充个人分配数据

        Args:
            ws: 工作表对象
            resource_data: 资源数据
            monthly_allocations: 月度分配数据
        """
        # 获取所有月份用于列映射
        all_months = sorted(set(alloc.month for alloc in monthly_allocations))
        month_to_col = {month: 4 + i for i, month in enumerate(all_months)}

        # 计算角色汇总占用的行数
        role_count = len(resource_data['role_summary'])
        start_row = 7 + role_count + 1  # 角色汇总后留一行空白

        current_row = start_row

        for i, individual in enumerate(resource_data['individuals'], 1):
            ws.cell(row=current_row, column=2, value=i)  # 编号
            ws.cell(row=current_row, column=3, value=individual['name'])  # 姓名
            ws.cell(row=current_row, column=4, value=individual['role'])  # 角色

            # 填充月度工时
            for month, hours in individual['monthly_hours'].items():
                if month in month_to_col and hours > 0:
                    col = month_to_col[month]
                    ws.cell(row=current_row, column=col, value=hours)

            current_row += 1

        # 添加合計行
        total_row = current_row + 1
        try:
            ws.merge_cells(start_row=total_row, start_column=2,
                          end_row=total_row, end_column=3)
            ws.cell(row=total_row, column=2, value="合計")
        except Exception as e:
            self.logger.warning(f"合并合計行失败: {e}")
            ws.cell(row=total_row, column=2, value="合計")

    # ======================== 新的保持格式的填充方法 ========================

    def _fill_cover_summary_data_only(self, ws, summary_data: Dict):
        """
        只填充表紙的数据，保持原格式不变

        Args:
            ws: 表紙工作表对象
            summary_data: 汇总数据
        """
        if 'monthly' not in summary_data:
            return

        self.logger.info("填充表紙汇总数据...")

        # 根据模板分析，从第4行开始是数据区域
        start_row = 4
        current_row = start_row

        # 按月份排序并填充数据
        sorted_months = sorted(summary_data['monthly'].keys())
        for month in sorted_months:
            month_data = summary_data['monthly'][month]

            # A列: 月份 (2024-01格式)
            if ws.cell(row=current_row, column=1).value is None:
                ws.cell(row=current_row, column=1, value=month.strftime("%Y-%m"))

            # B列: 总人数
            if ws.cell(row=current_row, column=2).value is None:
                ws.cell(row=current_row, column=2, value=month_data['person_count'])

            # C列: 总成本
            if ws.cell(row=current_row, column=3).value is None:
                ws.cell(row=current_row, column=3, value=month_data['cost'])

            current_row += 1

        # 填充合计行 (根据模板找到合计行位置)
        total_row = current_row + 1
        if ws.cell(row=total_row, column=1).value is None or "合计" in str(ws.cell(row=total_row, column=1).value):
            ws.cell(row=total_row, column=2, value=summary_data['total_person_months'])
            ws.cell(row=total_row, column=3, value=summary_data['total_cost'])

    def _fill_revision_history_data_only(self, ws):
        """
        只填充改訂履歴的数据，保持原格式不变

        Args:
            ws: 改訂履歴工作表对象
        """
        self.logger.info("填充改訂履歴数据...")

        try:
            # 生成改定履历条目
            revision_entry = self._generate_revision_entry({}, None)

            # 填充到第4行（根据模板分析，第3行是标题行）
            revision_row = 4
            if revision_entry:
                # 只填充数据，不改变格式，检查是否为合并单元格
                try:
                    # B列: 版本号（根据截图分析）
                    cell_b = ws.cell(row=revision_row, column=2)
                    if not hasattr(cell_b, 'coordinate') or cell_b.coordinate not in ws.merged_cells:
                        cell_b.value = revision_entry.version
                        self.logger.info(f"填充B列版本号: {revision_entry.version}")

                    # F列: 日期（根据截图分析）
                    cell_f = ws.cell(row=revision_row, column=6)
                    if not hasattr(cell_f, 'coordinate') or cell_f.coordinate not in ws.merged_cells:
                        cell_f.value = revision_entry.date
                        self.logger.info(f"填充F列日期: {revision_entry.date}")

                    # G列: 作成者（根据截图分析）
                    cell_g = ws.cell(row=revision_row, column=7)
                    if not hasattr(cell_g, 'coordinate') or cell_g.coordinate not in ws.merged_cells:
                        cell_g.value = revision_entry.creator
                        self.logger.info(f"填充G列作成者: {revision_entry.creator}")

                    # C、D、E、H列保持空白（变更箇所、変更内容、変更理由、備考）

                except Exception as cell_error:
                    self.logger.warning(f"填充单元格时出现警告: {cell_error}")
                    # 使用简单方式直接赋值（根据截图修正列位置）
                    ws.cell(row=revision_row, column=2).value = revision_entry.version
                    ws.cell(row=revision_row, column=6).value = revision_entry.date
                    ws.cell(row=revision_row, column=7).value = revision_entry.creator

        except Exception as e:
            self.logger.error(f"填充改訂履歴失败: {e}")
            raise

    def _fill_work_plan_data_only(self, ws, monthly_allocations: List[MonthlyAllocation]):
        """
        动态更新作業工程別要員計画工作表，包括时间轴和数据

        Args:
            ws: 作業工程別要員計画工作表对象
            monthly_allocations: 月度分配数据
        """
        self.logger.info("动态更新作業工程別要員計画...")

        # 获取所有月份并排序
        all_months = sorted(set(alloc.month for alloc in monthly_allocations))
        if not all_months:
            self.logger.warning("没有找到月度分配数据")
            return

        # 步骤1: 清理原有数据和合并单元格
        self._clear_work_plan_template(ws)

        # 步骤2: 重建时间轴（年份和月份标题）
        self._rebuild_time_axis(ws, all_months)

        # 步骤3: 填充工程数据
        self._fill_work_plan_data(ws, monthly_allocations, all_months)

        self.logger.info("作業工程別要員計画动态更新完成")

    def _safe_fill_cell(self, ws, row: int, column: int, value):
        """
        安全地填充单元格，避免合并单元格错误

        Args:
            ws: 工作表对象
            row: 行号
            column: 列号
            value: 要填充的值
        """
        try:
            cell = ws.cell(row=row, column=column)
            # 检查是否为合并单元格
            cell_coordinate = cell.coordinate

            # 检查当前单元格是否在合并范围内
            is_merged = False
            for merged_range in ws.merged_cells.ranges:
                if cell_coordinate in merged_range:
                    is_merged = True
                    # 如果是合并单元格，尝试填充到左上角单元格
                    top_left = merged_range.start_cell
                    if top_left.row == row and top_left.column == column:
                        # 当前就是左上角，可以填充
                        cell.value = value
                    # 如果不是左上角，跳过填充
                    break

            if not is_merged:
                # 不是合并单元格，直接填充
                cell.value = value

        except Exception as e:
            self.logger.warning(f"填充单元格 ({row}, {column}) 失败: {e}")

    def _fill_resource_plan_data_only(self, ws, monthly_allocations: List[MonthlyAllocation]):
        """
        只填充リソース計画的数据，保持原格式不变

        Args:
            ws: リソース計画工作表对象
            monthly_allocations: 月度分配数据
        """
        self.logger.info("填充リソース計画数据...")

        # 准备资源数据
        resource_data = self._prepare_resource_plan_data(monthly_allocations)

        # 获取所有月份用于列映射 (从E列开始，E=5列，根据模板分析)
        all_months = sorted(set(alloc.month for alloc in monthly_allocations))
        month_to_col = {month: 5 + i for i, month in enumerate(all_months)}  # E列=5, F列=6, ...

        # 1. 填充角色汇总数据（从第6行开始）
        current_row = 6
        sorted_roles = sorted(resource_data['role_summary'].keys())

        for role in sorted_roles:
            self._safe_fill_cell(ws, current_row, 4, role)  # D列: 角色名称

            # 填充月度工时数据
            role_monthly_data = resource_data['role_summary'][role]
            for month, hours in role_monthly_data.items():
                if month in month_to_col:
                    col = month_to_col[month]
                    self._safe_fill_cell(ws, current_row, col, hours)

            current_row += 1

        # 2. 填充个人分配数据（角色汇总后留一行空白）
        start_row = current_row + 1
        current_row = start_row

        for i, individual in enumerate(resource_data['individuals'], 1):
            self._safe_fill_cell(ws, current_row, 2, i)  # B列: 编号
            self._safe_fill_cell(ws, current_row, 3, individual['name'])  # C列: 姓名
            self._safe_fill_cell(ws, current_row, 4, individual['role'])  # D列: 角色

            # 填充月度工时
            for month, hours in individual['monthly_hours'].items():
                if month in month_to_col and hours > 0:
                    col = month_to_col[month]
                    self._safe_fill_cell(ws, current_row, col, hours)

            current_row += 1

    def _fill_resource_plan_data_only_dynamic(self, ws, monthly_allocations: List[MonthlyAllocation]):
        """
        动态更新リソース計画工作表，包括时间轴和数据
        Args:
            ws: リソース計画工作表对象
            monthly_allocations: 月度分配数据
        """
        self.logger.info("动态更新リソース計画...")

        # 获取所有月份并排序
        all_months = sorted(set(alloc.month for alloc in monthly_allocations))
        if not all_months:
            self.logger.warning("没有找到月度分配数据")
            return

        # 步骤1: 清理原有数据，保护公式
        self._clear_resource_plan_template(ws)

        # 步骤2: 重建时间轴
        self._rebuild_resource_plan_time_axis(ws, all_months)

        # 步骤3: 动态扩展模板并填充个人分配数据（方案B：拆分填入）
        self._fill_resource_plan_individual_data_dynamic(ws, monthly_allocations, all_months)

        self.logger.info("リソース計画动态更新完成")

    def _clear_resource_plan_template(self, ws):
        """
        清理リソース計画工作表的原有数据，保护公式
        Args:
            ws: 工作表对象
        """
        try:
            # 清理原有的合并单元格（保留前3行的基本标题合并）
            merged_ranges_to_remove = []
            for merged_range in ws.merged_cells.ranges:
                # 移除第4行开始的合并单元格，但保留特定的重要合并
                if merged_range.min_row >= 4:
                    # 保留重要的合并单元格：B6:B10, C6:C10, B21:C21
                    range_str = str(merged_range)
                    if range_str not in ['B6:B10', 'C6:C10', 'B21:C21']:
                        merged_ranges_to_remove.append(merged_range)

            for merged_range in merged_ranges_to_remove:
                try:
                    ws.unmerge_cells(str(merged_range))
                except:
                    pass

            # 清理原有数据
            cleared_cells = 0

            # 清空个人分配区域（第11-20行），只保护B列的ROW()公式
            for row in range(11, 21):  # 原模板范围
                for col in range(1, ws.max_column + 1):
                    # 只保护B列的ROW()公式
                    if col == 2:  # B列
                        continue
                    # 清理所有其他数据（包括公式和普通值）
                    cell = ws.cell(row=row, column=col)
                    if cell.value is not None:
                        cell.value = None
                        cleared_cells += 1

            # 清空第4、5行的时间轴标题（E列开始）
            for col in range(5, ws.max_column + 1):  # E列开始
                ws.cell(row=4, column=col).value = None
                ws.cell(row=5, column=col).value = None

            self.logger.info(f"リソース計画清理完成：删除了{len(merged_ranges_to_remove)}个合并单元格，清空了{cleared_cells}个数据单元格，保护了公式")
        except Exception as e:
            self.logger.error(f"清理リソース計画失败: {e}")

    def _move_total_row_to_extended_position(self, ws):
        """
        将原合计行从第21行移动到第31行，并更新公式
        Args:
            ws: 工作表对象
        """
        try:
            # 复制第21行的内容到第31行
            for col in range(1, ws.max_column + 1):
                source_cell = ws.cell(row=21, column=col)
                target_cell = ws.cell(row=31, column=col)

                if source_cell.value is not None:
                    if str(source_cell.value).startswith('='):
                        # 更新公式中的范围：E11:E20 -> E11:E30
                        formula = str(source_cell.value)
                        # 更新SUM公式的范围
                        if 'SUM(' in formula and '11:' in formula and '20)' in formula:
                            new_formula = formula.replace('11:E20', '11:E30').replace('11:F20', '11:F30').replace('11:G20', '11:G30').replace('11:H20', '11:H30').replace('11:I20', '11:I30').replace('11:J20', '11:J30').replace('11:K20', '11:K30').replace('11:L20', '11:L30').replace('11:M20', '11:M30').replace('11:N20', '11:N30').replace('11:O20', '11:O30').replace('11:P20', '11:P30').replace('11:Q20', '11:Q30').replace('11:R20', '11:R30').replace('11:S20', '11:S30').replace('11:T20', '11:T30').replace('11:U20', '11:U30')
                            target_cell.value = new_formula
                            self.logger.debug(f"更新公式 {chr(64+col)}21: {formula} -> {new_formula}")
                        else:
                            target_cell.value = source_cell.value
                    else:
                        target_cell.value = source_cell.value

                # 清空原第21行
                source_cell.value = None

            # 如果第21行有合并单元格，也需要移动
            merged_ranges_to_move = []
            for merged_range in ws.merged_cells.ranges:
                if merged_range.min_row == 21 and merged_range.max_row == 21:
                    merged_ranges_to_move.append(merged_range)

            for merged_range in merged_ranges_to_move:
                # 删除原合并
                ws.unmerge_cells(str(merged_range))
                # 在新位置创建合并
                ws.merge_cells(start_row=31, start_column=merged_range.min_col,
                             end_row=31, end_column=merged_range.max_col)

            self.logger.info("合计行已从第21行移动到第31行，公式已更新")
        except Exception as e:
            self.logger.error(f"移动合计行失败: {e}")

    def _rebuild_resource_plan_time_axis(self, ws, all_months):
        """
        重建リソース計画的时间轴（年份和月份标题）
        Args:
            ws: 工作表对象
            all_months: 所有月份列表
        """
        try:
            if not all_months:
                return

            start_col = 5  # E列开始
            current_col = start_col

            # 按年份分组
            year_groups = {}
            for month in all_months:
                year = month.year
                if year not in year_groups:
                    year_groups[year] = []
                year_groups[year].append(month)

            # 填充年份和月份标题
            for year in sorted(year_groups.keys()):
                year_months = sorted(year_groups[year])
                year_start_col = current_col

                # 填充月份标题（第5行）
                for month in year_months:
                    ws.cell(row=5, column=current_col, value=f"{month.month}月")
                    current_col += 1

                # 填充年份标题（第4行），合并对应的月份列
                year_end_col = current_col - 1
                if year_start_col == year_end_col:
                    # 只有一个月，直接填充
                    ws.cell(row=4, column=year_start_col, value=f"{year}年")
                else:
                    # 多个月，填充并合并
                    ws.cell(row=4, column=year_start_col, value=f"{year}年")
                    ws.merge_cells(start_row=4, start_column=year_start_col,
                                 end_row=4, end_column=year_end_col)

            months_count = len(all_months)
            self.logger.info(f"リソース計画时间轴重建完成，覆盖了{months_count}个月")
        except Exception as e:
            self.logger.error(f"重建リソース計画时间轴失败: {e}")

    def _fill_resource_plan_individual_data(self, ws, monthly_allocations, all_months):
        """
        填充リソース計画的个人分配数据（方案B：拆分填入）
        Args:
            ws: 工作表对象
            monthly_allocations: 月度分配数据
            all_months: 所有月份列表
        """
        try:
            # 建立月份到列的映射
            month_to_col = {month: 5 + i for i, month in enumerate(all_months)}

            # 按角色分组并生成个人条目
            role_groups = {}
            for alloc in monthly_allocations:
                role = alloc.role
                if role not in role_groups:
                    role_groups[role] = []
                role_groups[role].append(alloc)

            current_row = 11  # 从第11行开始填充个人数据
            role_counters = {}  # 每个角色的计数器

            for role in sorted(role_groups.keys()):
                role_allocations = role_groups[role]
                role_counters[role] = 1  # 初始化该角色的计数器

                # 按月份汇总该角色的需求
                monthly_totals = {}
                for alloc in role_allocations:
                    month = alloc.month
                    if month not in monthly_totals:
                        monthly_totals[month] = 0
                    monthly_totals[month] += alloc.person_count

                # 方案B：为每个人创建单独的行
                for month, person_count in monthly_totals.items():
                    for person_index in range(int(person_count)):
                        if current_row > 30:  # 扩充后限制在第30行以内
                            self.logger.warning("个人分配数据超出扩充后的模板范围，停止填充")
                            return

                        # 填充个人信息（使用角色独立的计数器）
                        person_name = f"{role}担当者{role_counters[role]}"
                        role_counters[role] += 1
                        ws.cell(row=current_row, column=3, value=person_name)  # C列: 担当者
                        ws.cell(row=current_row, column=4, value=role)  # D列: 役割

                        # 填充该月的工时（每人1工时）
                        if month in month_to_col:
                            col = month_to_col[month]
                            ws.cell(row=current_row, column=col, value=1)

                        current_row += 1

            filled_rows = current_row - 11
            self.logger.info(f"リソース計画个人分配数据填充完成，填充了{filled_rows}行")
        except Exception as e:
            self.logger.error(f"填充リソース計画个人分配数据失败: {e}")

    def _fill_resource_plan_individual_data_dynamic(self, ws, monthly_allocations, all_months):
        """
        动态扩展模板并填充リソース計画的个人分配数据（方案B：拆分填入）
        Args:
            ws: 工作表对象
            monthly_allocations: 月度分配数据
            all_months: 所有月份列表
        """
        try:
            # 建立月份到列的映射
            month_to_col = {month: 5 + i for i, month in enumerate(all_months)}

            # 按角色分组并计算总需求
            role_groups = {}
            total_persons_needed = 0
            for alloc in monthly_allocations:
                role = alloc.role
                if role not in role_groups:
                    role_groups[role] = []
                role_groups[role].append(alloc)

            # 计算总共需要多少担当者
            for role in role_groups.keys():
                role_allocations = role_groups[role]
                monthly_totals = {}
                for alloc in role_allocations:
                    month = alloc.month
                    if month not in monthly_totals:
                        monthly_totals[month] = 0
                    monthly_totals[month] += alloc.person_count

                # 计算该角色需要的总人数
                role_person_count = sum(monthly_totals.values())
                total_persons_needed += role_person_count

            self.logger.info(f"总共需要{total_persons_needed}个担当者")

            # 计算需要的行数
            original_individual_rows = 10  # 原模板第11-20行
            required_rows = total_persons_needed

            if required_rows > original_individual_rows:
                # 需要扩展模板
                new_total_row = 11 + required_rows  # 新的合计行位置
                self.logger.info(f"需要扩展模板：从{original_individual_rows}行扩展到{required_rows}行，合计行移动到第{new_total_row}行")

                # 移动合计行到新位置
                self._move_total_row_to_new_position(ws, 21, new_total_row, required_rows)
            else:
                new_total_row = 21  # 保持原位置

            # 填充个人分配数据
            current_row = 11  # 从第11行开始填充个人数据
            role_counters = {}  # 每个角色的计数器

            for role in sorted(role_groups.keys()):
                role_allocations = role_groups[role]
                role_counters[role] = 1  # 初始化该角色的计数器

                # 按月份汇总该角色的需求
                monthly_totals = {}
                for alloc in role_allocations:
                    month = alloc.month
                    if month not in monthly_totals:
                        monthly_totals[month] = 0
                    monthly_totals[month] += alloc.person_count

                # 方案B：为每个人创建单独的行
                for month, person_count in monthly_totals.items():
                    for person_index in range(int(person_count)):
                        if current_row >= new_total_row:  # 不能超过合计行
                            self.logger.warning(f"个人分配数据达到合计行（第{new_total_row}行），停止填充")
                            return

                        # 填充个人信息（使用角色独立的计数器）
                        person_name = f"{role}担当者{role_counters[role]}"
                        role_counters[role] += 1

                        # 生成B列的ROW()公式
                        ws.cell(row=current_row, column=2, value=f"=ROW()-10")  # B列: 序号公式
                        ws.cell(row=current_row, column=3, value=person_name)  # C列: 担当者
                        ws.cell(row=current_row, column=4, value=role)  # D列: 役割

                        # 填充该月的工时（每人1工时）
                        if month in month_to_col:
                            col = month_to_col[month]
                            ws.cell(row=current_row, column=col, value=1)

                        current_row += 1

            filled_rows = current_row - 11
            self.logger.info(f"リソース計画个人分配数据填充完成，填充了{filled_rows}行，扩展到第{new_total_row-1}行")
        except Exception as e:
            self.logger.error(f"填充リソース計画个人分配数据失败: {e}")

    def _move_total_row_to_new_position(self, ws, old_row, new_row, individual_rows):
        """
        将合计行移动到新位置并更新公式
        Args:
            ws: 工作表对象
            old_row: 原合计行位置
            new_row: 新合计行位置
            individual_rows: 个人分配行数
        """
        try:
            # 复制原合计行的内容到新位置
            for col in range(1, ws.max_column + 1):
                source_cell = ws.cell(row=old_row, column=col)
                target_cell = ws.cell(row=new_row, column=col)

                # 检查源单元格是否有值且不是合并单元格
                if source_cell.value is not None:
                    value_to_set = source_cell.value

                    if str(source_cell.value).startswith('='):
                        # 更新公式中的范围
                        formula = str(source_cell.value)
                        if 'SUM(' in formula and 'E11:E20' in formula:
                            # 更新范围到新的个人分配区域
                            new_end_row = 10 + individual_rows  # E11开始，所以是10+行数
                            new_formula = formula.replace('E11:E20', f'E11:E{new_end_row}').replace('F11:F20', f'F11:F{new_end_row}').replace('G11:G20', f'G11:G{new_end_row}').replace('H11:H20', f'H11:H{new_end_row}').replace('I11:I20', f'I11:I{new_end_row}').replace('J11:J20', f'J11:J{new_end_row}').replace('K11:K20', f'K11:K{new_end_row}').replace('L11:L20', f'L11:L{new_end_row}').replace('M11:M20', f'M11:M{new_end_row}').replace('N11:N20', f'N11:N{new_end_row}').replace('O11:O20', f'O11:O{new_end_row}').replace('P11:P20', f'P11:P{new_end_row}').replace('Q11:Q20', f'Q11:Q{new_end_row}').replace('R11:R20', f'R11:R{new_end_row}').replace('S11:S20', f'S11:S{new_end_row}').replace('T11:T20', f'T11:T{new_end_row}').replace('U11:U20', f'U11:U{new_end_row}')
                            value_to_set = new_formula
                            self.logger.debug(f"更新公式 {chr(64+col)}{new_row}: {formula} -> {new_formula}")

                    # 安全填充目标单元格
                    self._safe_fill_cell(ws, new_row, col, value_to_set)

                # 安全清空原位置（避免合并单元格错误）
                self._safe_fill_cell(ws, old_row, col, None)

            # 处理合并单元格
            merged_ranges_to_move = []
            for merged_range in ws.merged_cells.ranges:
                if merged_range.min_row == old_row and merged_range.max_row == old_row:
                    merged_ranges_to_move.append(merged_range)

            for merged_range in merged_ranges_to_move:
                # 删除原合并
                ws.unmerge_cells(str(merged_range))
                # 在新位置创建合并
                ws.merge_cells(start_row=new_row, start_column=merged_range.min_col,
                             end_row=new_row, end_column=merged_range.max_col)

            self.logger.info(f"合计行已从第{old_row}行移动到第{new_row}行，公式已更新为{individual_rows}行范围")
        except Exception as e:
            self.logger.error(f"移动合计行失败: {e}")

    def _cleanup_workbook_for_excel_compatibility(self, wb):
        """
        清理工作簿以提高Excel兼容性

        Args:
            wb: 工作簿对象
        """
        try:
            # 简化的兼容性清理：只处理基本的数据清理
            for ws in wb.worksheets:
                # 确保所有单元格值都是有效的
                for row in ws.iter_rows():
                    for cell in row:
                        if cell.value is not None:
                            # 确保数值类型正确
                            if isinstance(cell.value, (int, float)):
                                if str(cell.value) == 'nan':  # 简化NaN检查
                                    cell.value = 0
                            elif isinstance(cell.value, str):
                                # 清理可能导致问题的字符
                                try:
                                    # 移除null字符
                                    cell.value = cell.value.replace('\x00', '').replace('\x01', '').replace('\x02', '')
                                except:
                                    pass

            self.logger.info("工作簿兼容性清理完成")

        except Exception as e:
            self.logger.warning(f"工作簿兼容性清理失败: {e}")

    def _save_with_strict_compatibility(self, wb, output_file):
        """
        使用严格兼容性模式保存文件

        Args:
            wb: 工作簿对象
            output_file: 输出文件路径
        """
        try:
            # 创建新的工作簿，只复制基本数据
            from openpyxl import Workbook
            new_wb = Workbook()

            # 移除默认工作表
            if 'Sheet' in new_wb.sheetnames:
                new_wb.remove(new_wb['Sheet'])

            # 复制每个工作表的基本数据
            for ws_name in wb.sheetnames:
                old_ws = wb[ws_name]
                new_ws = new_wb.create_sheet(title=ws_name)

                # 复制单元格数据
                for row in old_ws.iter_rows():
                    for cell in row:
                        if cell.value is not None:
                            new_cell = new_ws.cell(row=cell.row, column=cell.column)
                            new_cell.value = cell.value

                # 复制基本的合并单元格
                try:
                    for merged_range in old_ws.merged_cells.ranges:
                        new_ws.merge_cells(str(merged_range))
                except Exception as merge_error:
                    self.logger.warning(f"复制合并单元格失败: {merge_error}")

            # 保存新工作簿
            new_wb.save(output_file)
            new_wb.close()

        except Exception as e:
            self.logger.error(f"严格兼容性保存失败: {e}")
            raise

    def _clear_work_plan_template(self, ws):
        """
        清理作業工程別要員計画工作表的原有数据和合并单元格

        Args:
            ws: 工作表对象
        """
        try:
            # 清理原有的合并单元格（保留标题部分的基本合并）
            merged_ranges_to_remove = []
            for merged_range in ws.merged_cells.ranges:
                # 移除第4行开始的所有合并单元格（包括数据区域和时间轴）
                if merged_range.min_row >= 4:
                    merged_ranges_to_remove.append(merged_range)

            for merged_range in merged_ranges_to_remove:
                try:
                    ws.unmerge_cells(str(merged_range))
                except:
                    pass

            # 清空数据区域（第6行开始）- 保护V列（第22列）的公式
            cleared_cells = 0
            for row in range(6, ws.max_row + 1):
                for col in range(1, ws.max_column + 1):
                    # 保护V列（第22列）的合计公式不被清理
                    if col == 22:  # V列
                        continue
                    if ws.cell(row=row, column=col).value is not None:
                        ws.cell(row=row, column=col).value = None
                        cleared_cells += 1

            # 清空第4、5行的年份和月份标题（F列开始）- 保护V列合计标题
            for col in range(6, ws.max_column + 1):
                # 保护V列（第22列）的"合計"标题不被清理
                if col == 22:  # V列
                    continue
                ws.cell(row=4, column=col).value = None
                ws.cell(row=5, column=col).value = None

            self.logger.info(f"工作表清理完成：删除了{len(merged_ranges_to_remove)}个合并单元格，清空了{cleared_cells}个数据单元格，保护了V列合计功能")

        except Exception as e:
            self.logger.error(f"清理工作表失败: {e}")

    def _rebuild_time_axis(self, ws, all_months):
        """
        重建时间轴（年份和月份标题）

        Args:
            ws: 工作表对象
            all_months: 所有月份列表
        """
        try:
            if not all_months:
                return

            start_col = 6  # F列开始
            current_col = start_col

            # 获取年份范围
            start_year = all_months[0].year
            end_year = all_months[-1].year

            # 设置年份标题和月份标题
            for year in range(start_year, end_year + 1):
                year_months = [m for m in all_months if m.year == year]
                if not year_months:
                    continue

                year_start_col = current_col
                year_end_col = current_col + len(year_months) - 1

                # 设置年份标题（第4行）
                if year_start_col == year_end_col:
                    # 只有一个月，不需要合并
                    ws.cell(row=4, column=year_start_col, value=f"{year}年")
                else:
                    # 多个月，需要合并单元格
                    try:
                        ws.merge_cells(start_row=4, start_column=year_start_col,
                                     end_row=4, end_column=year_end_col)
                        ws.cell(row=4, column=year_start_col, value=f"{year}年")
                    except Exception as merge_error:
                        self.logger.warning(f"合并年份标题失败: {merge_error}")
                        ws.cell(row=4, column=year_start_col, value=f"{year}年")

                # 设置月份标题（第5行）
                for month in year_months:
                    ws.cell(row=5, column=current_col, value=f"{month.month}月")
                    current_col += 1

            self.logger.info(f"时间轴重建完成，覆盖了{len(all_months)}个月")

        except Exception as e:
            self.logger.error(f"重建时间轴失败: {e}")

    def _fill_work_plan_data(self, ws, monthly_allocations, all_months):
        """
        填充工程数据

        Args:
            ws: 工作表对象
            monthly_allocations: 月度分配数据
            all_months: 所有月份列表
        """
        try:
            # 准备工程数据
            work_phases = self._prepare_work_phases_data(monthly_allocations)

            # 建立月份到列的映射
            month_to_col = {month: 6 + i for i, month in enumerate(all_months)}

            current_row = 6  # 从第6行开始填充数据

            for phase_data in work_phases:
                phase_start_row = current_row

                # 为每个角色创建一行
                for role in phase_data['roles']:
                    # 填充基本信息（只在第一行填充）
                    if current_row == phase_start_row:
                        # 序号、工程名称、期间
                        ws.cell(row=current_row, column=2, value=phase_data['no'])  # B列: No.
                        ws.cell(row=current_row, column=3, value=phase_data['phase_name'])  # C列: 工程名称
                        ws.cell(row=current_row, column=4, value=phase_data['period_desc'])  # D列: 期间

                    # 角色
                    ws.cell(row=current_row, column=5, value=role)  # E列: 角色

                    # 填充月份数据
                    for alloc in phase_data['allocations']:
                        if alloc.role == role and alloc.month in month_to_col:
                            col = month_to_col[alloc.month]
                            # 填入实际的人员数量
                            if alloc.person_count > 0:
                                ws.cell(row=current_row, column=col, value=alloc.person_count)

                    current_row += 1

            self.logger.info(f"工程数据填充完成，填充了{len(work_phases)}个工程阶段")

        except Exception as e:
            self.logger.error(f"填充工程数据失败: {e}")